
package com.cx.bank.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.cx.bank.hibernateModel.PageModel;
import com.cx.bank.hibernateModel.Transactions;
import com.cx.bank.hibernateModel.Users;
import com.cx.bank.model.InfoBean;
import com.cx.bank.model.MoneyBean;
import com.cx.bank.model.UserBean;
import com.cx.bank.util.HibernateFilter;
import com.cx.bank.util.HibernateUtils;
import com.cx.bank.util.JdbcUtils;
import com.cx.bank.util.MD5;

/**
 * 持久层 连接数据库，实现注册、登录功能
 **/
public class MySQlDao implements BankDaoInterface {
	Session session = null;
	Query query = null;
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement st = null;
	UserBean userBean = null;
	MoneyBean mo = MoneyBean.getInstance();

	public MySQlDao() {

	}

	// 登陆方法
	public Boolean login(UserBean user) throws IOException {
		// 从模型层对象user中取得用户名和密码
		String rename = user.getName();
		String repsd = MD5.getMD5(user.getPassword());

		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();

			String hql = "from Users u where u.username = :name and u.password = :password";
			query = session.createQuery(hql);
			query.setParameter("name", rename);
			query.setParameter("password", repsd);
			List<Users> userList = query.list();

			if (!userList.isEmpty()) {
				// 将用户余额存入moneyBean对象
				for (Users u : userList) {
					mo.setCount(u.getMoney());
					System.out.println("Id:" + u.getId() + "    账号:" + u.getUsername() + "   余额:" + u.getMoney());
				}
				System.out.println("登录成功");
				return true;
			} else {
				System.out.println("账号或密码错误，登录失败");
			}
		} catch (Exception e) {
			session.getTransaction().rollback();
		}
		return false;
	}

	// 注册方法
	public Boolean register(UserBean user) throws IOException {
		// 从模型层对象user中取得用户名和密码
		Users users = null;
		String rename = user.getName();
		String repsd = MD5.getMD5(user.getPassword());
		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();

			String hql = "select username from Users u where u.username = :name ";
			query = session.createQuery(hql);
			query.setParameter("name", rename);
			List<Object> userList = query.list();

			if (userList.isEmpty()) {
				users = new Users();
				users.setUsername(rename);
				users.setPassword(repsd);
				users.setMoney(0);

				session.save(users);
				session.getTransaction().commit();
				System.out.println("注册成功");
				return true;
			} else {
				System.out.println("用户已存在，注册失败");
			}
		} catch (Exception e) {
			session.getTransaction().rollback();
		}
		return false;
	}

	// 本账号数据库信息配置保存 即存款、取款、转账的金额转出
	public void savaInformation(UserBean user, MoneyBean money) throws IOException {
		Users users = null;
		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();

			String hql = "from Users u where u.username = :name";
			query = session.createQuery(hql);
			query.setParameter("name", user.getName());
			List<Users> userList = query.list();

			for (Users u : userList) {
				users = u;
			}
			users.setMoney(money.getCount());

			session.update(users);
			session.getTransaction().commit();
			System.out.println("金额存储成功");
		} catch (Exception e) {
			session.getTransaction().rollback();
			System.out.println("金额存储失败");
		}
	}

	// 转入账号数据库信息配置保存 即 金额转入
	public Boolean savaInformation2(String transfername, Double transfermoney) throws IOException {
		Users users = null;

		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();

			String hql = "from Users u where u.username = :name";
			query = session.createQuery(hql);
			query.setParameter("name", transfername);
			List<Users> userList = query.list();
			System.out.println(userList.isEmpty());

			if (userList.isEmpty()) {
				System.out.println("用户不存在，转账失败");
				return false;
			} else {
				for (Users u : userList) {
					users = u;
				}
				users.setMoney(users.getMoney() + transfermoney);

				session.update(users);
				session.getTransaction().commit();
				System.out.println("接收转账成功");
				return true;
			}
		} catch (Exception e) {
			session.getTransaction().rollback();
		}
		return false;
	}

	// 保存用户的存款、取款操作记录
	public void savaTransactionDetials2(String name, String transaction_details, String transaction_time)
			throws IOException {
		Users users = null;
		Transactions transaction = null;

		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();

			String hql = "from Users u where u.username = :name";
			query = session.createQuery(hql);
			query.setParameter("name", name);
			List<Users> userList = query.list();
			for (Users u : userList) {
				users = u;
			}
			transaction = new Transactions();
			transaction.setTransaction_details(transaction_details);
			transaction.setTransaction_time(transaction_time);
			transaction.setUsers(users);

			session.save(transaction);
			session.getTransaction().commit();
			System.out.println("账号记录存储成功");

		} catch (Exception e) {
			session.getTransaction().rollback();
			System.out.println("账号记录存储失败");
		}

	}

	// 保存用户的转账操作记录
	public void savaTransactionDetials(String name, String transfername, String transaction_details,
			String transaction_details2, String transaction_time) throws IOException {
		Users users = null;
		Transactions transaction = null;
		Boolean flag = false;
		try {
			session = HibernateFilter.getSession();
			Transaction tx = session.beginTransaction();
			if (transfername != null && transaction_details2 != null) {
				String hql = "from Users u where u.username = :name";
				query = session.createQuery(hql);
				query.setParameter("name", name);
				List<Users> userList = query.list();
				for (Users u : userList) {
					users = u;
				}
				transaction = new Transactions();
				transaction.setTransaction_details(transaction_details);
				transaction.setTransaction_time(transaction_time);
				transaction.setUsers(users);

				session.save(transaction);
				tx.commit();
				flag = true;
				System.out.println("转出记录存储成功");
			}

			Transaction tx2 = session.beginTransaction();
			if (flag) {
				String hql = "from Users u where u.username = :name";
				query = session.createQuery(hql);
				query.setParameter("name", transfername);
				List<Users> userList = query.list();
				for (Users u : userList) {
					users = u;
				}
				transaction = new Transactions();
				transaction.setTransaction_details(transaction_details2);
				transaction.setTransaction_time(transaction_time);
				transaction.setUsers(users);

				session.save(transaction);
				tx2.commit();
				flag = true;
				System.out.println("转入账号记录存储成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("交易记录失败，用户输入了错误的账户！");
		} finally {
			JdbcUtils.closeResource(conn, st, rs);
		}
	}

	// 从数据库中查询交易明细
	public List<InfoBean> inquiryDetials(String name) {
		Query query = null;
		List<InfoBean> infoList = new ArrayList<InfoBean>();
		try {
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();

			String hql = "from Transactions transactions "
					+ "where transactions.users.username = ?";
			query = session.createQuery(hql);
			query.setParameter(0, name);
			
			List<Transactions> userList = query.list();
			for(Transactions infos :userList) {
				InfoBean info = new InfoBean();
				info.setUsername(name);
				info.setTransaction_details(infos.getTransaction_details());
				info.setTransaction_time(infos.getTransaction_time());
				info.setTransaction_state("完成");
				
				infoList.add(info);
			}
			
			/*String hql = "select user.username,transactions.transaction_details,transactions.transaction_time "
			+ "from Users user,Transactions transactions "
			+ "where users_id = (select id from Users where username = ?) and username = ?";
			query.setParameter(1, name);
			List<Object[]> userList = query.list();
			for (Object[] infos : userList) {
				info = new InfoBean();
				info.setUsername((String) infos[0]);
				info.setTransaction_details((String) infos[1]);
				info.setTransaction_time((String) infos[2]);
				info.setTransaction_state("完成");
				infoList.add(info);
			}*/
			return infoList;
		} catch (Exception e) {
			System.out.println("服务器启动失败");
		}
		return infoList;
	}

	/**
	 * 根据用户名查询交易信息--分页查询
	 * 
	 * @param queryStr 查询条件
	 * @return item对象的集合
	 */
	public PageModel findAllTransaction(int pageNo, int pageSize, String name,String orderby) {
		Session session = null;
		PageModel pageModel = null;
		List<InfoBean> infoList = new ArrayList<InfoBean>();
		try {
			session = HibernateFilter.getSession();
			session.beginTransaction();
			Query query = null;
			
			String hql = "from Transactions transactions "
					+ "where transactions.users.username = ? ORDER by transactions.id "+orderby;
			query = session.createQuery(hql);
			query.setParameter(0, name);
			query.setFirstResult((pageNo - 1) * pageSize);
			query.setMaxResults(pageSize);
			
			List<Transactions> userList = query.list();
			for(Transactions infos :userList) {
				InfoBean info = new InfoBean();
				info.setUsername(name);
				info.setTransaction_details(infos.getTransaction_details());
				info.setTransaction_time(infos.getTransaction_time());
				info.setTransaction_state("完成");
				
				infoList.add(info);
			}
			
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setList(infoList);
			pageModel.setTotalRecords(getTotalRecords(session, name));
			
			session.getTransaction().commit();
		} catch (Exception e) {
			// 记录日志,log4j等......
			e.printStackTrace();
			session.getTransaction().rollback();
		}
		return pageModel;

	}

	/**
	 * 查询记录数
	 * 
	 * @param session
	 * @param queryStr
	 * @return
	 */
	private int getTotalRecords(Session session, String name) {
		Query query = null;
		String hql = "select count(*) "
				+ "from Users user,Transactions transactions "
				+ "where users_id = (select id from Users where username = ?) and username = ?";
		query = session.createQuery(hql);
		query.setParameter(0, name);
		query.setParameter(1, name);

		Long count = (Long) query.uniqueResult();
		return count.intValue();
	}
}